package com.ck.ips.service.impl.store;

import com.ck.common.model.DeviceGroup;
import com.ck.common.model.DeviceStore;
import com.ck.common.model.OperationResult;
import com.ck.common.model.Store;
import com.ck.core.constant.BusinessConstant;
import com.ck.core.constant.ClientConstant;
import com.ck.core.constant.ExcelSuffixEnum;
import com.ck.core.constant.MsgConstant;
import com.ck.core.context.UserContextHolder;
import com.ck.core.exception.ServiceException;
import com.ck.core.util.FileUtils;
import com.ck.core.util.HuaWeiCloudUtils;
import com.ck.ips.dao.store.StoreMapper;
import com.ck.ips.model.vo.StoreVO;
import com.ck.ips.model.vo.uploadexcel.DeviceExcelVO;
import com.ck.ips.model.vo.uploadexcel.ExcelErrVO;
import com.ck.ips.service.DeviceGroupService;
import com.ck.ips.service.store.StoreService;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

import static com.ck.core.util.FileUtils.returnBitMap;

/**
 * @description: 门店service
 * @author: zhanglei
 * @date: 2019/4/29 14:29
 */
@Service(value="storeService")
public class StoreServiceImpl implements StoreService {
    private Logger logger = LoggerFactory.getLogger(StoreServiceImpl.class);

    @Autowired
    private StoreMapper storeMapper;
    @Autowired
    private DeviceGroupService deviceGroupService;

    @Override
    public Page<StoreVO> listByPage(String storeName, Integer status, Integer pageNum, Integer pageSize) {
        PageHelper.startPage(pageNum, pageSize);
        return storeMapper.listByPageAll(storeName,status);
    }

    @Override
    public OperationResult<Object> createStroe(String storeName, String storeFunctionary,String cellPhone,String settledPhone,String fax, String province, String city, String area, String longitude, String latitude, String storeAdd,String storePhoto, Long coveredPopulation) {
        //登陆用户id
        Long loginUserId = UserContextHolder.currentUser().getUserId();
        //当前时间戳
        Long nowTime = System.currentTimeMillis();
        if(StringUtils.isBlank(storeName)){
            return OperationResult.newValidateFailOperationResult("门店名称不能为空");
        }
        if(StringUtils.isBlank(province) || StringUtils.isBlank(city) || StringUtils.isBlank(storeAdd)){
            return OperationResult.newValidateFailOperationResult("门店位置信息不能为空");
        }
        //查询门店名称是否已经被使用
        Long storeExistsId = this.getStoreIdByName(storeName);
        if(storeExistsId!=null){
            return OperationResult.newValidateFailOperationResult("门店名称已经被使用");
        }
        //将门店信息录入store表中
        Store oneStore = new Store();
        oneStore.setStoreName(storeName);
        oneStore.setStoreFunctionary(storeFunctionary);
        oneStore.setCellPhone(cellPhone);
        oneStore.setSettledPhone(settledPhone);
        oneStore.setFax(fax);
        oneStore.setProvince(province);
        oneStore.setCity(city);
        oneStore.setArea(area);
        oneStore.setLongitude(longitude);
        oneStore.setLatitude(latitude);
        oneStore.setStoreAdd(storeAdd);
        oneStore.setStorePhoto(storePhoto);
        oneStore.setCreatePerson(loginUserId);
        oneStore.setCreateTime(nowTime);
        oneStore.setUpdatePerson(loginUserId);
        oneStore.setUpdateTime(nowTime);
        oneStore.setStatus(0);
        oneStore.setCoveredPopulation(coveredPopulation);
        storeMapper.createStroe(oneStore);
        Long storeId = oneStore.getStoreId();
        //构造门店的序列号
        String serialNum = String.format("%04d", storeId);
        //修改门店序列号信息
        this.updateStoreSerialNum(storeId,serialNum);
        //创建分组
        DeviceGroup deviceGroup = new DeviceGroup();
        deviceGroup.setGroupName(storeName);
        deviceGroupService.saveDeviceGroup(deviceGroup);
        String msg = "创建门店["+storeName+"]成功";
        return OperationResult.newSuccessTipsOperationResult(msg);
    }

    @Override
    public OperationResult<Object> updateStore(Long storeId, String storeName, String storeFunctionary,String cellPhone,String settledPhone,String fax, String province, String city, String area, String longitude, String latitude, String storeAdd,String storePhoto, Long coveredPopulation) {
        //登陆用户id
        Long loginUserId = UserContextHolder.currentUser().getUserId();
        //当前时间戳
        Long nowTime = System.currentTimeMillis();
        if(storeId==null){
            return OperationResult.newValidateFailOperationResult("门店id不能为空");
        }
        if(StringUtils.isBlank(storeName)){
            return OperationResult.newValidateFailOperationResult("门店名称不能为空");
        }
        if(StringUtils.isBlank(province) || StringUtils.isBlank(city)  || StringUtils.isBlank(storeAdd)){
            return OperationResult.newValidateFailOperationResult("门店位置信息不能为空");
        }
        //查询门店名称是否已经被使用
        Long storeExistsId = this.getStoreIdByName(storeName);
        if(storeExistsId!=null && !storeExistsId.equals(storeId)){
            return OperationResult.newValidateFailOperationResult("门店名称已经被使用");
        }
        //修改门店信息
        Store oneStore = new Store();
        //修改门店信息
        oneStore.setStoreId(storeId);
        oneStore.setStoreName(storeName);
        oneStore.setStoreFunctionary(storeFunctionary);
        oneStore.setCellPhone(cellPhone);
        oneStore.setSettledPhone(settledPhone);
        oneStore.setFax(fax);
        oneStore.setProvince(province);
        oneStore.setCity(city);
        oneStore.setArea(area);
        oneStore.setLongitude(longitude);
        oneStore.setLatitude(latitude);
        oneStore.setStoreAdd(storeAdd);
        oneStore.setStorePhoto(storePhoto);
        oneStore.setUpdatePerson(loginUserId);
        oneStore.setUpdateTime(nowTime);
        oneStore.setCoveredPopulation(coveredPopulation);
        storeMapper.updateStore(oneStore);
        String msg = "修改门店["+storeName+"]成功";
        return OperationResult.newSuccessTipsOperationResult(msg);
    }

    @Override
    public StoreVO getStoreById(Long storeId) {
        return storeMapper.getStoreById(storeId);
    }

    @Override
    public OperationResult<Object> updateStatus(Long storeId, Integer status) {
        //修改数据库内容
        if(status == null){
            return OperationResult.newValidateFailOperationResult("门店状态不能为空");
        }
        StoreVO storeVo = this.getStoreById(storeId);
        if(storeVo!=null){
            storeMapper.updateStatus(storeId,status);
            //添加修改日志
            String msg = "修改门店["+storeVo.getStoreName()+"]状态为["+(status==0?"启用":"禁用")+"]成功";
            return OperationResult.newSuccessTipsOperationResult(msg);
        }else{
            return OperationResult.newValidateFailOperationResult("门店不存在");
        }
    }

    @Override
    public OperationResult<Object> deleteStore(Long storeId) {
        //删除门店信息
        if(storeId == null){
            return OperationResult.newValidateFailOperationResult("门店id不能为空");
        }
        //当导入的excel表中使用了该门店的id则不允许删除
        int count = this.getCountByDeviceStore(storeId);
        if(count>0){
            return OperationResult.newValidateFailOperationResult("该门店已经导入过设备，无法删除");
        }
        StoreVO storeVo = this.getStoreById(storeId);
        if(storeVo!=null){
            storeMapper.deleteStore(storeId);
            //添加log日志
            String msg = "删除门店["+storeVo.getStoreName()+"]成功";
            return OperationResult.newSuccessTipsOperationResult(msg);
        }else{
            return OperationResult.newValidateFailOperationResult("门店不存在");
        }
    }

    @Override
    public DeviceExcelVO deviceStoreExcel(String path, String originalFilename) throws ServiceException {
        DeviceExcelVO deviceExcelVO = new DeviceExcelVO();
        List<DeviceStore> deviceStoresList = new ArrayList<>();
        List<ExcelErrVO> excelErrVOList = new ArrayList<>();
        ExcelErrVO excelErrVO = new ExcelErrVO();
        Workbook workbook = null;
        try {
            if (originalFilename.endsWith(ExcelSuffixEnum.SUFFIX_2003.getValue())) {
                workbook = new HSSFWorkbook(returnBitMap(path));
            } else if (originalFilename.endsWith(ExcelSuffixEnum.SUFFIX_2007.getValue())) {
                workbook = new XSSFWorkbook(returnBitMap(path));
            }
        } catch (IOException e) {
            throw new ServiceException("格式错误");
        }
        if(workbook == null) {
            throw new ServiceException("格式错误");
        } else {
            //获取所有的工作空间的的数量
            int numOfSheet = workbook.getNumberOfSheets();
            //循环页
            for(int i = 0 ; i < numOfSheet ; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                int lastRowNum = sheet.getLastRowNum();
                // 从第二行开始遍历获取值，第一行为标题（循环行）
                for(int j = 1 ; j <= lastRowNum ; j++) {
                    Row row = sheet.getRow(j);
                    //如果整行为空则跳过
                    if(!checkExcelRowIsNull(row,10)){
                        continue;
                    }
                    short haveValue = row.getFirstCellNum();
                    if(haveValue == -1){
                        continue;
                    }
                    DeviceStore deviceStore = new DeviceStore();
                    //mac地址
                    if(row.getCell(0) != null){
                        row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                        String deviceMac = row.getCell(0).getStringCellValue();
                        if("".equals(deviceMac.trim())){
                            excelErrVO = new ExcelErrVO(i,j,0,"mac地址不能为空");
                            excelErrVOList.add(excelErrVO);
                            deviceExcelVO.setStatus(1);
                        }else {
                            //检测mac地址是否被使用过
                            Integer count = storeMapper.checkDeviceMac(deviceMac);
                            if(count>0){
                                excelErrVO = new ExcelErrVO(i,j,0,"mac地址已经被使用");
                                excelErrVOList.add(excelErrVO);
                                deviceExcelVO.setStatus(1);
                            }
                        }
                        deviceStore.setDeviceMac(deviceMac);
                    }else{
                        excelErrVO = new ExcelErrVO(i,j,0,"mac地址不能为空");
                        excelErrVOList.add(excelErrVO);
                        deviceExcelVO.setStatus(1);
                    }
                    //设备型号
                    if(row.getCell(1) != null){
                        row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                        String model = row.getCell(1).getStringCellValue();
                        if("".equals(model.trim())){
                            excelErrVO = new ExcelErrVO(i,j,1,"设备型号不能为空");
                            excelErrVOList.add(excelErrVO);
                            deviceExcelVO.setStatus(1);
                        }
                        deviceStore.setModel(model);
                    }else{
                        excelErrVO = new ExcelErrVO(i,j,1,"设备型号不能为空");
                        excelErrVOList.add(excelErrVO);
                        deviceExcelVO.setStatus(1);
                    }
                    //设备类型
                    if(row.getCell(2) != null){
                        row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                        String type = row.getCell(2).getStringCellValue();
                        if("".equals(type.trim())){
                            excelErrVO = new ExcelErrVO(i,j,2,"设备类型不能为空");
                            excelErrVOList.add(excelErrVO);
                            deviceExcelVO.setStatus(1);
                        }
                        deviceStore.setType(type);
                    }else{
                        excelErrVO = new ExcelErrVO(i,j,2,"设备类型不能为空");
                        excelErrVOList.add(excelErrVO);
                        deviceExcelVO.setStatus(1);
                    }
                    //设备尺寸
                    if(row.getCell(3) != null){
                        row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
                        String screeSize = row.getCell(3).getStringCellValue();
                        if("".equals(screeSize.trim())){
                            excelErrVO = new ExcelErrVO(i,j,3,"设备尺寸不能为空");
                            excelErrVOList.add(excelErrVO);
                            deviceExcelVO.setStatus(1);
                        }
                        deviceStore.setScreeSize(screeSize);
                    }else{
                        excelErrVO = new ExcelErrVO(i,j,3,"设备尺寸不能为空");
                        excelErrVOList.add(excelErrVO);
                        deviceExcelVO.setStatus(1);
                    }
                    //分辨率
                    if(row.getCell(4) != null){
                        row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
                        String resolvingPower = row.getCell(4).getStringCellValue();
                        if("".equals(resolvingPower.trim())){
                            excelErrVO = new ExcelErrVO(i,j,4,"分辨率不能为空");
                            excelErrVOList.add(excelErrVO);
                            deviceExcelVO.setStatus(1);
                        }else {
                            //正则匹配1234x1234
                            String pattern = "^\\d{1,}x\\d{1,}$";
                            boolean r = Pattern.matches(pattern,resolvingPower.trim());
                            if(!r){
                                excelErrVO = new ExcelErrVO(i,j,4,"分辨率格式不正确");
                                excelErrVOList.add(excelErrVO);
                                deviceExcelVO.setStatus(1);
                            }
                        }
                        deviceStore.setResolvingPower(resolvingPower.trim());
                    }else{
                        excelErrVO = new ExcelErrVO(i,j,4,"分辨率不能为空");
                        excelErrVOList.add(excelErrVO);
                        deviceExcelVO.setStatus(1);
                    }
                    //楼栋
                    if(row.getCell(5) != null){
                        row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
                        String building = row.getCell(5).getStringCellValue();
                        deviceStore.setBuilding(building);
                    }
                    //单元
                    if(row.getCell(6) != null){
                        row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
                        String unit = row.getCell(6).getStringCellValue();
                        deviceStore.setUnit(unit);
                    }
                    //楼层
                    if(row.getCell(7) != null){
                        row.getCell(7).setCellType(Cell.CELL_TYPE_STRING);
                        String floor = row.getCell(7).getStringCellValue();
                        deviceStore.setFloor(floor);
                    }
                    //具体位置
                    if(row.getCell(8) != null){
                        row.getCell(8).setCellType(Cell.CELL_TYPE_STRING);
                        String position = row.getCell(8).getStringCellValue();
                        deviceStore.setPosition(position);
                    }
                    //设备名称
                    if(row.getCell(9) != null){
                        row.getCell(9).setCellType(Cell.CELL_TYPE_STRING);
                        String deviceName = row.getCell(9).getStringCellValue();
                        deviceStore.setDeviceName(deviceName);
                    }
                    //点位码
                    if(row.getCell(10) != null){
                        row.getCell(10).setCellType(Cell.CELL_TYPE_STRING);
                        String addCode = row.getCell(10).getStringCellValue();
                        deviceStore.setAddCode(addCode);
                    }
                    //将一行的信息装入列表中
                    deviceStoresList.add(deviceStore);
                }
            }
        }
        deviceExcelVO.setDeviceStoreList(deviceStoresList);
        deviceExcelVO.setExcelErrVOList(excelErrVOList);
        return deviceExcelVO;
    }

    /**
     *判断excel一整行是否含有非空值（排除空格）
     */
    private Boolean checkExcelRowIsNull(Row row,int num){
        for(int i = 0; i <= num; i++) {
            if(row == null){
                return false;
            }
            Cell cell = row.getCell(i);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK && !cell.getStringCellValue().trim().isEmpty()) {
                return true;
            }
        }
        return false;
    }

    @Override
    public OperationResult<Object> excelErrRed(List<ExcelErrVO> excelErrVOList, String path, String originalFilename) throws IOException {
        if(StringUtils.isBlank(path)){
            return new OperationResult<>(ClientConstant.CODE_PARAM_VALIDATE_FAIL,"文件路径为空");
        }
        //获取原文件
        String tempName;
        File file;
        FileOutputStream output;
        String cloudFileDir;
        String url;
        Map<String, Object> data;
        InputStream inputStream = returnBitMap(path);
        if(null == inputStream){
            return new OperationResult<>(ClientConstant.CODE_PARAM_VALIDATE_FAIL,"文件不存在");
        }
        try (Workbook workbook = new XSSFWorkbook(inputStream)) {
            Sheet sheet = null;
            Row row = null;
            Cell cell = null;

            for (ExcelErrVO errVo : excelErrVOList) {
                CellStyle cellStyle = workbook.createCellStyle();
                cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                cellStyle.setFillForegroundColor(HSSFColor.RED.index);
                sheet = workbook.getSheetAt(errVo.getPage());
                row = sheet.getRow(errVo.getRow());
                cell = row.getCell(errVo.getLine());
                if (cell == null) {
                    cell = row.createCell(errVo.getLine());
                        XSSFRichTextString text = new XSSFRichTextString("");
                        cell.setCellValue(text);
                }
                cell.setCellStyle(cellStyle);
            }
            //将文件写入本地
            Long nowTime = System.currentTimeMillis();
            //本地文件地址
            tempName = BusinessConstant.TEMP_DIR + "/" + nowTime + originalFilename;
            file = new File(tempName);
            if(!file.createNewFile()){
                logger.error("excelErrRed 创建本地文件失败");
            }
            //输出Excel文件 
            output = new FileOutputStream(tempName);
            //写入磁盘  
            workbook.write(output);
            output.close();
            //将文件上传到华为云
            //上传的文件夹
            cloudFileDir = "tmp/excel/";
            url = HuaWeiCloudUtils.uploadFile2HuaweiCloud(file, cloudFileDir);
            FileUtils.delFile(tempName);
            data = new HashMap<>(2);
            data.put("url",url);
            data.put("list",excelErrVOList);
        } finally {
            inputStream.close();
        }
        return new OperationResult<>(ClientConstant.CODE_PARAM_VALIDATE_FAIL,"部分导入信息错误，请下载查看失败数据",data);
    }

    @Override
    public OperationResult<Object> addDeviceStore(List<DeviceStore> deviceStoreList, Long storeId) {
        storeMapper.addDeviceStore(deviceStoreList,storeId);
        //获取所有终端mac存日志
        String devices = "";
        for(DeviceStore deviceStore:deviceStoreList){
            devices+=deviceStore.getDeviceMac()+",";
        }
        return OperationResult.newSuccessTipsOperationResult("上传终端,mac:"+devices);
    }

    @Override
    public void updateStoreSerialNum(Long storeId, String serialNum) {
        storeMapper.updateStoreSerialNum(storeId,serialNum);
    }

    @Override
    public Long getStoreIdByName(String storeName) {
        return storeMapper.getStoreIdByName(storeName);
    }

    @Override
    public int getCountByDeviceStore(Long storeId) {
        return storeMapper.getCountByDeviceStore(storeId);
    }

    @Override
    public OperationResult<Object> uploadExcelDevice(String path, Long storeId) {
        if(StringUtils.isBlank(path)) {
            return OperationResult.newValidateFailOperationResult(MsgConstant.FILE_NOT_NULL);
        }
        // 获取文件名字,用来验证文件格式
        String originalFilename = "";
        int start = path.lastIndexOf("/");
        if (start != -1 ) {
            originalFilename = path.substring(start + 1);
        }
        if("".equals(originalFilename)){
            return OperationResult.newValidateFailOperationResult(MsgConstant.FILE_NOT_NULL);
        }
        //解析完成的类
        DeviceExcelVO deviceExcelVO = new DeviceExcelVO();
        try{
            deviceExcelVO = this.deviceStoreExcel(path,originalFilename);
        } catch (ServiceException e) {
            return OperationResult.newValidateFailOperationResult(e.getMessage());
        }
        if(deviceExcelVO.getStatus() != 0){
            List<ExcelErrVO> excelErrVOList = deviceExcelVO.getExcelErrVOList();
            try{
                return this.excelErrRed(excelErrVOList,path,originalFilename);
            } catch (IOException e) {
                return OperationResult.newValidateFailOperationResult(e.getMessage());
            }
        }else{
            return OperationResult.newSuccessOperationResult(deviceExcelVO.getDeviceStoreList());
        }
    }

    @Override
    public OperationResult<Object> uploadExcelDeviceSure(String path, Long storeId) {
        if(StringUtils.isBlank(path)) {
            return OperationResult.newValidateFailOperationResult(MsgConstant.FILE_NOT_NULL);
        }
        // 获取文件名字,用来验证文件格式
        String originalFilename = "";
        int start = path.lastIndexOf("/");
        if (start != -1 ) {
            originalFilename = path.substring(start + 1);
        }
        if("".equals(originalFilename)){
            return OperationResult.newValidateFailOperationResult(MsgConstant.FILE_NOT_NULL);
        }
        //解析完成的类
        DeviceExcelVO deviceExcelVO = new DeviceExcelVO();
        try{
            deviceExcelVO = this.deviceStoreExcel(path,originalFilename);
        } catch (ServiceException e) {
            return OperationResult.newValidateFailOperationResult(e.getMessage());
        }
        if(deviceExcelVO.getStatus() != 0){
            //含有错误信息,将excel处理后直接返回
            return OperationResult.newValidateFailOperationResult("excel中包含错误信息，请确认");
        }else{
            //正常情况，将数据写入数据库
            List<DeviceStore> deviceStoreList = deviceExcelVO.getDeviceStoreList();
            return this.addDeviceStore(deviceStoreList,storeId);
        }
    }

    @Override
    public XSSFWorkbook getStoreList(String storeName, Integer status) {
        List<StoreVO> storeVOList =  storeMapper.listByPageAll(storeName,status);
        //构造excel并赋值
        //表头数据
        String[] header = {"编号", "门店名称", "门店序列号", "电话", "状态","导入设备","激活设备",};
        //声明一个工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        //生成一个表格，设置表格名称
        XSSFSheet sheet = workbook.createSheet("门店列表");
        //设置表格列宽度为20个字节
        sheet.setDefaultColumnWidth(30);
        //创建第一行表头
        XSSFRow headrow = sheet.createRow(0);
        //遍历添加表头
        for (int i = 0; i < header.length; i++) {
            //创建一个单元格
            XSSFCell cell = headrow.createCell(i);
            //创建一个内容对象
            XSSFRichTextString text = new XSSFRichTextString(header[i]);
            //将内容对象的文字内容写入到单元格中
            cell.setCellValue(text);
        }
        //向excel表中装入数据
        Integer i=0;
        for(StoreVO vo : storeVOList){
            i++;
            //建一行
            XSSFRow headrow1 = sheet.createRow(i);
            //建一个单元格
            XSSFCell cell = headrow1.createCell(0);
            XSSFRichTextString text = new XSSFRichTextString(vo.getStoreId().toString());
            cell.setCellValue(text);
            //建一个单元格
            cell = headrow1.createCell(1);
            text = new XSSFRichTextString(vo.getStoreName());
            cell.setCellValue(text);
            //建一个单元格
            cell = headrow1.createCell(2);
            text = new XSSFRichTextString(vo.getSerialNum());
            cell.setCellValue(text);
            //建一个单元格
            cell = headrow1.createCell(3);
            text = new XSSFRichTextString(vo.getCellPhone());
            cell.setCellValue(text);
            //建一个单元格
            cell = headrow1.createCell(4);
            text = new XSSFRichTextString((vo.getStatus() !=null && vo.getStatus() == 0) ? "启用" : "禁用");
            cell.setCellValue(text);
            //建一个单元格
            cell = headrow1.createCell(5);
            text = new XSSFRichTextString(String.valueOf(vo.getUploadExcelDeviceNum()));
            cell.setCellValue(text);
            //建一个单元格
            cell = headrow1.createCell(6);
            text = new XSSFRichTextString(String.valueOf(vo.getRegistDeviceNum()));
            cell.setCellValue(text);
        }
        return workbook;
    }
}
